package fh.app4stud.mensa.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.util.Log;

/**
 * Creates Database with table "menu" from an xml file.
 *
 * Created by IntelliJ IDEA.
 * User: ivan plese
 * Date: 22.04.11
 * Time: 20:31
 * To change this template use File | Settings | File Templates.
 */
public class MenuDbAdapter {

    public static final String KEY_ROWID = "_id";
    public static final String KEY_LOC_ID = "_loc_id";
    public static final String KEY_LOC_NAME = "loc_name";
    public static final String KEY_DAY_ID = "_day_id";
    public static final String KEY_DAY_NAME = "day_name";
    public static final String KEY_MENU_ID = "_menu_id";
    public static final String KEY_MENU_NAME = "menu_name";
    public static final String KEY_PRICE = "price";
    public static final String KEY_ITEM = "item";

    public static final String KEY_CITY = "city";
    public static final String KEY_STREET = "street";
    public static final String KEY_HOUSE_NR = "house_nr";
    public static final String KEY_ZIP = "zip_code";
    public static final String KEY_LATITUDE = "latitude";
    public static final String KEY_LONGITUDE = "longitude";

    private static final String TAG = "DATA BASE MenuDbAdapter";

    private static final String DATABASE_NAME = "data";
    private static final String DATABASE_TABLE_LOC = "locations";
    private static final String DATABASE_TABLE = "menu";
    private static final int DATABASE_VERSION = 2;

    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    private static final String DATABASE_CREATE =
            "create table menu (" +
                    "_id integer primary key autoincrement, " +
                    "_loc_id integer not null," +
                    "loc_name text not null," +
                    "_day_id integer not null," +
                    "day_name text not null," +
                    "_menu_id integer not null," +
                    "menu_name text not null," +
                    "price text not null," +
                    "item text not null);";

    private static final String DATABASE_CREATE_LOC =
            "create table " + DATABASE_TABLE_LOC + " (" +
                    "_id integer primary key autoincrement," +

                    "loc_name text not null," +
                    "city text not null," +
                    "street text not null," +
                    "house_nr integer not null," +
                    "zip_code integer not null," +
                    "latitude real not null," +
                    "longitude real not null);";

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db){
           db.execSQL(DATABASE_CREATE_LOC);
           db.execSQL(DATABASE_CREATE);

        }

         @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS menu;");
            db.execSQL("DROP TABLE IF EXISTS locations;");
            onCreate(db);
        }
    }

    /**
     * Constructor - /takes the context to allow the database to be
     * opened/created
     *
     * @param ctx the Context within which to work
     */
    public MenuDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

/**
     * Open the items database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     *
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws android.database.SQLException if the database could be neither opened or created
     */
    public MenuDbAdapter  open() throws SQLException {
        Log.i(TAG, " Constructor of data base adapter ");
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        mDbHelper.close();
    }

    /**
     * Create a new menu item using the title and body provided. If the menu is
     * successfully created return the new rowId for that menu, otherwise return
     * a -1 to indicate failure.
     *
     * @param loc_id location id
     * @param loc_name  location name
     * @param day_id    day id
     * @param day_name   day name
     * @param menu_id    menu id
     * @param menu_name  menu name
     * @param price  price
     * @param item  item name
     * @return      cursor with all the data in the database
     */
    public long createItem( int loc_id, String loc_name, int day_id , String day_name, int menu_id, String menu_name, String price, String item){
        Log.i(TAG, "In Create Item");
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_LOC_ID, loc_id);
        initialValues.put(KEY_LOC_NAME, loc_name);
        initialValues.put(KEY_DAY_ID, day_id);
        initialValues.put(KEY_DAY_NAME, day_name);
        initialValues.put(KEY_MENU_ID, menu_id);
        initialValues.put(KEY_MENU_NAME, menu_name);
        initialValues.put(KEY_PRICE, price);
        initialValues.put(KEY_ITEM, item);

        return mDb.insert(DATABASE_TABLE, null, initialValues);
    }

     public long createLocation( String loc_name, String city, String street, int house_nr, int zip, float lat, float lon ){
        Log.i(TAG, "In Create Locations");
        ContentValues initialValues = new ContentValues();

        initialValues.put(KEY_LOC_NAME, loc_name);
        initialValues.put(KEY_CITY, city);
         initialValues.put(KEY_STREET, street);
         initialValues.put(KEY_HOUSE_NR, house_nr);
         initialValues.put(KEY_ZIP, zip);
         initialValues.put(KEY_LATITUDE, lat);
         initialValues.put(KEY_LONGITUDE, lon);

        return mDb.insert(DATABASE_TABLE_LOC, null, initialValues);
    }

    //SELECT * FROM "test_table" WHERE "lastname" = 'last1' AND address='add2';
    public Cursor rawQueryByDayAndLoc(String location, int dayId){
        Cursor mCursor =
                mDb.rawQuery(
                        "SELECT "+KEY_DAY_NAME+","+KEY_MENU_ID+","+KEY_MENU_NAME+","+KEY_PRICE+","+KEY_ITEM+" FROM ( " +

                                "SELECT "+KEY_DAY_NAME+","+KEY_LOC_ID+","+KEY_DAY_ID+","+KEY_MENU_ID+","+KEY_MENU_NAME+","+KEY_PRICE+","+KEY_ITEM +" FROM "+DATABASE_TABLE+" WHERE "+KEY_LOC_NAME+"=?) " +

                                "WHERE "+KEY_DAY_ID+"="+dayId ,

                                new String[]{location});
        return mCursor;
    }

    public Cursor fetchAllLocations() {

        return mDb.query(DATABASE_TABLE_LOC, new String[] {KEY_LOC_NAME, KEY_CITY, KEY_STREET, KEY_HOUSE_NR, KEY_ZIP, KEY_LONGITUDE, KEY_LATITUDE}, null, null, null, null, null);
    }

    public Cursor fetchLocationsByCity(String city){
          Cursor mCursor =
                mDb.rawQuery(
                        "SELECT "+KEY_LOC_NAME+","+KEY_CITY+","+KEY_STREET+","+KEY_HOUSE_NR+","+KEY_ZIP+","+KEY_LATITUDE+","+KEY_LONGITUDE

                                +" FROM " + DATABASE_TABLE_LOC + "WHERE "+KEY_CITY+"=?" ,

                                new String[]{city});
        return mCursor;
    }

    public Cursor fetchCityNames(){

        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        String qery = qb.buildQueryString(true, DATABASE_TABLE_LOC, new String[]{KEY_CITY}, null, null,null, KEY_CITY +" ASC", null);
        Cursor mCursor =  mDb.rawQuery(qery, null);
        return mCursor;

    }

    public void deleteRecords(String tableName){
        //every monday or sunday the records are replaced with new ones
        mDb.execSQL("DROP TABLE IF EXISTS " + tableName);
        mDb.execSQL(DATABASE_CREATE);
    }
}
